﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Data.Common;
using System.Data;

public class BaseInfoCtrl
{

    public static bool AuthenticateUser(string username)
    {
        Database db = DatabaseFactory.CreateDatabase();

        DbCommand dbCommand;
        string sql = string.Format("SELECT AllowWebAccess FROM aspnet_UserInfo WHERE username='{0}'", username);
        dbCommand = db.GetSqlStringCommand(sql);

        object obj = db.ExecuteScalar(dbCommand);
        if (obj == null || obj == DBNull.Value)
            return false;
        else
        {
            return (bool)db.ExecuteScalar(dbCommand);
        }

    }
    /// <summary>
    /// 根据ID获取供应商名称
    /// </summary>
    /// <returns></returns>
    public static string GetCiceroniNameById(string ciceroniId)
    {

        Database db = DatabaseFactory.CreateDatabase();

        DbCommand dbCommand;
        string sql = string.Format("SELECT [CiceroniName] FROM bas_Ciceroni WHERE CiceroniID={0}", ciceroniId);
        dbCommand = db.GetSqlStringCommand(sql);

        return db.ExecuteScalar(dbCommand).ToString();
    }



    /// <summary>
    /// 获取全部导游信息
    /// </summary>
    /// <returns></returns>
    public static DataTable GetCiceroniData()
    {
        Database db = DatabaseFactory.CreateDatabase();

        DbCommand dbCommand;
        string sql = "SELECT [CiceroniID], [CiceroniName], [Sex], [Birthday], [CiceroniLanguage], [IDcardNo], [CiceroniNo], [Phone], [EMail], [Remark], [CiceroniLevel], [City] FROM bas_Ciceroni";
        dbCommand = db.GetSqlStringCommand(sql);

        DataSet ds = db.ExecuteDataSet(dbCommand);
        if (ds.Tables.Count > 0)
            return ds.Tables[0];
        else
            return null;
    }
    /// <summary>
    /// 从导游信息表中获取全部导游的语言信息
    /// </summary>
    /// <returns>只含‘CiceroniLanguage’字段的DataTable</returns>
    public static DataTable GetCiceroniLanguageData()
    {

        Database db = DatabaseFactory.CreateDatabase();

        DbCommand dbCommand;
        string sql = "SELECT distinct [CiceroniLanguage] FROM bas_Ciceroni";
        dbCommand = db.GetSqlStringCommand(sql);

        DataSet ds = db.ExecuteDataSet(dbCommand);
        if (ds.Tables.Count > 0)
            return ds.Tables[0];
        else
            return null;
    }
    /// <summary>
    /// 从导游信息表中获取全部导游的姓名信息
    /// </summary>
    /// <returns>只含‘GetCiceroniNameData’字段的DataTable</returns>
    public static DataTable GetCiceroniNameData(string language)
    {

        Database db = DatabaseFactory.CreateDatabase();

        DbCommand dbCommand;

        //去掉了sql中的distinct
        string sql = string.Format("SELECT [CiceroniName],[CiceroniID] FROM bas_Ciceroni WHERE CiceroniLanguage LIKE '%{0}%'  ORDER BY CiceroniLevel Desc", language);
        dbCommand = db.GetSqlStringCommand(sql);

        DataSet ds = db.ExecuteDataSet(dbCommand);
        if (ds.Tables.Count > 0)
            return ds.Tables[0];
        else
            return null;
    }
    /// <summary>
    ///  获取变更通知表数据
    /// </summary>
    /// <returns></returns>
    public static DataTable GetChangeNotifyData(string tourGroupId)
    {

        Database db = DatabaseFactory.CreateDatabase();

        DbCommand dbCommand;
        string sql = string.Format("SELECT [ChangeNotifyID], [TourgroupID],[Number],[ChangeTime], [Title], [Context] FROM orgt_ChangeNotify WHERE TourgroupID='{0}'", tourGroupId);
        dbCommand = db.GetSqlStringCommand(sql);

        DataSet ds = db.ExecuteDataSet(dbCommand);
        if (ds.Tables.Count > 0)
            return ds.Tables[0];
        else
            return null;
    }

}